package recommendPackage

import org.apache.spark.sql.SparkSession

/**
 * @author 杨铭
 *         2023/6/20,15:31
 */
object SparkSQL {
  def main(args: Array[String]): Unit = {
    val Spark = SparkSession.builder
      .appName("Spark SQL Example")
      .master("local[*]")
      .getOrCreate()

    val jdbcDF = Spark.read
      .format("jdbc")
      .option("url", "jdbc:mysql://localhost:3306/recommend")
      .option("dbtable", "student")
      .option("user", "root")
      .option("password", "mysqlym123")
      .load()

    jdbcDF.createOrReplaceTempView("student")
    val result = Spark.sql("SELECT classId, SUM(CASE WHEN sex='1' THEN 1 ELSE 0 END) AS male_num, SUM(CASE WHEN sex='0' THEN 1 ELSE 0 END) AS female_num FROM student GROUP BY classId;")
    result.show()

    Spark.stop()
  }
}
